const express = require('express');
const multer = require('multer');
const mysql = require('mysql2/promise');
const path = require('path');

const app = express();
// 初始化MySQL连接池
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: '123456',
  database: 'html',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// 初始化数据库表
(async () => {
  try {
    const connection = await pool.getConnection();
    await connection.query(`CREATE TABLE IF NOT EXISTS classifications (
      id INT PRIMARY KEY AUTO_INCREMENT,
      keyword VARCHAR(255),
      category VARCHAR(50),
      timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
    )`);
    await connection.query(`DROP TABLE IF EXISTS garbage_types`);
    await connection.query(`DROP TABLE IF EXISTS garbage_types`);
    await connection.query(`CREATE TABLE garbage_types (
      id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(255) UNIQUE,
      category VARCHAR(50),
      handling_tips TEXT
    )`);
    connection.release();
    console.log('Connected to MySQL database');
    
    // 插入示例垃圾分类数据
    await connection.query('INSERT IGNORE INTO garbage_types (name, category, handling_tips) VALUES ?', [
  [
    ['报纸', '可回收物', '叠放整齐后回收'],
    ['电池', '有害垃圾', '投放到专用回收箱'],
    ['剩菜', '厨余垃圾', '沥干水分后投放'],
    ['塑料袋', '其他垃圾', '尽量重复使用'],
    ['玻璃瓶', '可回收物', '洗净后回收'],
    ['过期药品', '有害垃圾', '密封后投放'],
    ['果皮', '厨余垃圾', '放入堆肥桶'],
    ['纸巾', '其他垃圾', '污染后无法回收']
  ]
]);
  } catch (err) {
    console.error('Database connection error:', err);
  }
})();

// 配置静态文件服务
app.use(express.static('public'));

// 文本查询路由
app.post('/search', express.json(), async (req, res) => {
  try {
    const { keyword } = req.body;
    const [rows] = await pool.query(
      'SELECT category FROM garbage_types WHERE name = ?',
      [keyword]
    );

    if (rows.length > 0) {
      await pool.query(
        'INSERT INTO classifications (keyword, category) VALUES (?, ?)',
        [keyword, rows[0].category]
      );
      res.json({ category: rows[0].category });
    } else {
      res.status(404).json({ error: '未找到该物品分类' });
    }
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: '查询失败' });
  }
});



// 历史记录路由
app.get('/history', async (req, res) => {
  try {
    const [rows] = await pool.query('SELECT * FROM classifications ORDER BY timestamp DESC LIMIT 10');
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

const PORT = process.env.PORT || 3000;
// 统一错误处理中间件
app.use((err, req, res, next) => {
  console.error(err.stack);
  res.status(500).json({ error: '服务器内部错误' });
});

// 垃圾知识接口路由
app.get('/tips', async (req, res) => {
  try {
    const { q } = req.query;
    const [rows] = await pool.query(
      'SELECT name, category, handling_tips FROM garbage_types WHERE name LIKE ? ORDER BY name LIMIT 10',
      [`%${q}%`]
    );
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

app.listen(PORT, () => {
  console.log(`Server running on port ${PORT}`);
});